package main

import (
	"fmt"

	"gitee.com/chsir/base-spider-engine/config"
)

func createTable() {
	db := config.DB_MYSQL
	var tableName string
	var statusTableName string

	for i := 0; i < 16; i++ {
		// 页面表
		tableName = "web_pages_" + fmt.Sprintf("%x", i)
		result := db.Exec("CREATE TABLE `" + tableName + "` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `url` varchar(768) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL comment '网页链接',   `host` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL comment '域名',   `dic_done` tinyint DEFAULT '0' comment '字典已拆分',   `craw_done` tinyint NOT NULL DEFAULT '0' comment '已爬',   `craw_time` timestamp NOT NULL DEFAULT '2001-01-01 00:00:00' comment '爬取时间',   `origin_title` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL comment '上级页面超链接文字',   `parent_id` int NOT NULL DEFAULT '0' comment '上级页面id',   `scheme` varchar(255) DEFAULT NULL comment '协议：http/https',   `domain1` varchar(255) DEFAULT NULL comment '一级域名',   `domain2` varchar(255) DEFAULT NULL comment '二级域名',   `path` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL comment '路径',   `query` varchar(2000) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL comment '查询参数',   `title` varchar(1000) DEFAULT NULL comment '标题',   `text` longtext comment '页面内容',   `created_at` timestamp NOT NULL DEFAULT '2001-01-01 08:00:00',   PRIMARY KEY (`id`),   KEY `url` (`url`),   KEY `host_crtime` (`host`),   KEY `host_cdown` (`host`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;")
		fmt.Println("建表结果: ", tableName, result.RowsAffected)

		// 页面表的影子表 方便查询
		statusTableName = "web_status_" + fmt.Sprintf("%x", i)
		result1 := db.Exec("CREATE TABLE `" + statusTableName + "` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `url` varchar(767) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,   `host` varchar(255) DEFAULT NULL,   `craw_done` tinyint NOT NULL DEFAULT '0',   `craw_time` timestamp NOT NULL DEFAULT '2001-01-01 00:00:00',   PRIMARY KEY (`id`),   KEY `idx_host_crtime` (`host`,`craw_time`),   KEY `idx_url` (`url`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;")
		fmt.Println("建表结果: ", statusTableName, result1.RowsAffected)
	}

	// 初始化域名黑名单
	result4 := db.Exec("CREATE TABLE `domain_black_list` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `domain` varchar(255) DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;")
	fmt.Println("建表结果: ", "domain_black_list", result4.RowsAffected)

	// 初始化 kvstores
	result2 := db.Exec("CREATE TABLE `kvstores` (   `id` int unsigned NOT NULL AUTO_INCREMENT,   `key` varchar(255) DEFAULT NULL,   `val` varchar(255) DEFAULT NULL,   `time` timestamp NOT NULL DEFAULT '2001-01-01 00:00:01',   PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;")
	fmt.Println("建表结果: ", "kvstores", result2.RowsAffected)
	// 填充 kvstores
	result3 := db.Exec("INSERT INTO `kvstores` (`id`, `key`, `val`, `time`) VALUES   (1, 'stop', '0', '2022-09-04 01:27:55'),   (2, 'stopNew', '0', '2001-01-01 00:00:01'),   (3, 'stopWashDicRedisToMySQL', '0', '2001-01-01 00:00:01');")
	fmt.Println("填充结果: ", "kvstores", result3.RowsAffected)
}
